﻿using System;
using Cirrious.MvvmCross.Interfaces.ServiceProvider;
using Cirrious.MvvmCross.Plugins.Sqlite;
using System.Collections.Generic;
using System.Linq;
using TeaLightV5.Foundation.BL;
using Cirrious.MvvmCross.ExtensionMethods;
using TeaLightV5.Foundation.Sal.Interfaces;

namespace TeaLightV5.Foundation.DL
{
    /// <summary>
    /// TaskDatabase builds on SQLite.Net and represents a specific database, in our case, the TeaLight DB.
    /// It contains methods for retrieval and persistence as well as db creation, all based on the
    /// underlying ORM.
    /// </summary>
    public static class DatabaseBase  //: IMvxServiceConsumer<ISQLiteConnectionFactory> //: ISQLiteConnection
    {
        private static ISQLiteConnection _Me;
        internal static ISQLiteConnection Me
        {
            get
            {
                if (_Me == null)
                {
                    ISQLiteConnectionFactory m = MvxServiceProviderExtensions.GetService<ISQLiteConnectionFactory>();

                    _Me = m.Create("TeaLightDB.db3");
                }
                return _Me;
            }
        }

        private static readonly object Locker = new object();

        /// <summary>
        /// Initializes a new instance of the <see cref="DatabaseBase"/> TeaLightDatabase.
        /// if the database doesn't exist, it will create the database and all the tables.
        /// </summary>
        /// <param name='path'>
        /// Path.
        /// </param>
        //protected DatabaseBase()
        //{
        //    CreateTables();
        //}

        #region Create Tables

        //protected abstract void CreateTables();

        /// <summary>
        /// Executes a "create table if not exists" on the database. It also
        /// creates any specified indexes on the columns of the table. It uses
        /// a schema automatically generated from the specified type. You can
        /// later access this schema by calling GetMapping.
        /// </summary>
        /// <returns>
        /// The number of entries added to the database schema.
        /// </returns>
        public static int CreateTable<T>()
        {
            return Me.CreateTable<T>();
        }

        #endregion Create Tables

        public static IEnumerable<T> GetItems<T, TKey>()
            where T : IEntity<TKey>, new()
            
        {
            lock (Locker)
            {
                return (from i in Me.Table<T>() select i).ToList();
            }
        }

        public static T GetItem<T, TKey>(TKey id)
            where T : IEntity<TKey>, new()
            
        {
            lock (Locker)
            {
                // ---
                //return (from i in me.Table<T> ()
                //        where i.ID == id
                //        select i).FirstOrDefault ();

                // +++ To properly use Generic version and eliminate NotSupportedException
                // ("Cannot compile: " + expr.NodeType.ToString ()); in SQLite.cs
                return Me.Table<T>().FirstOrDefault(x => Equals(x.Id, id));
            }
        }

        public static ITableQuery<T> GetTable<T, TKey>()
            where T : IEntity<TKey>, new()
            
        {
            lock (Locker)
            {
                return Me.Table<T>();
            }
        }

        public static int SaveItem<T, TKey>(T item)
            where T : IEntity<TKey>, new()
            
        {
            lock (Locker)
            {
                if (!Equals(item.Id, default(TKey))
                    && !Equals(GetItem<T, TKey>(item.Id), default(T)))
                {
                    return Me.Update(item);
               }
                return Me.Insert(item);
            }
        }

        public static void SaveItems<T, TKey>(IEnumerable<T> items)
            where T : IEntity<TKey>, new()
            
        {
            lock (Locker)
            {
                Me.BeginTransaction();

                foreach (T item in items)
                {
                    SaveItem<T, TKey>(item);
                }

                Me.Commit();
            }
        }

        public static int DeleteItem<T, TKey>(TKey id)
            where T : IEntity<TKey>, new()
            
        {
            lock (Locker)
            {
                return Me.Delete<T>(id);
            }
        }

        public static void ClearTable<T, TKey>()
            where T : IEntity<TKey>, new()
            
        {
            lock (Locker)
            {
                Me.Execute(string.Format("delete from \"{0}\"", typeof(T).Name));
            }
        }

        // helper for checking if database has been populated
        public static int CountTable<T, TKey>()
            where T : IEntity<TKey>, new()
            
        {
            lock (Locker)
            {
                string sql = string.Format("select count (*) from \"{0}\"", typeof(T).Name);

                return Me.ExecuteScalar<int>(sql);
            }
        }
    }
}